﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.UIModel;
using CBS.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using System.Data.Common;

namespace CBS.APP
{
    public class StudentPaymentApp
    {
        public static bool CreateStudentPayment(StudentPaymentViewModel studentPayment)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var stuAmount = cbs.cbs_student_amount.FirstOrDefault(t => t.StudentId == studentPayment.StudentId);
                var teaAmount = cbs.cbs_teacher_amount.FirstOrDefault(t => t.TeacherId == studentPayment.TeacherId);

                var pic = new cbs_image { ImageData = studentPayment.PaymentPic };
                cbs.cbs_image.Add(pic);
                cbs.SaveChanges();

                var payment = new cbs_student_payment
                {
                    StudentId = studentPayment.StudentId,
                    TeacherId = studentPayment.TeacherId,
                    Payment = studentPayment.Payment,
                    PaymentTime = studentPayment.PaymentTime,
                    PaymentType = studentPayment.PaymentType,
                    PaymentCode = studentPayment.PaymentCode,
                    PaymentSemester = studentPayment.PaymentSemester,
                    PaymentPicId = pic.ImageId,
                    BeforeAmount = stuAmount.Balance,
                    LogTime = DateTime.Now,
                };
                cbs.cbs_student_payment.Add(payment);
                cbs.SaveChanges();

                stuAmount.Balance += studentPayment.Payment;
                teaAmount.Balance += studentPayment.Payment;
                cbs.SaveChanges();

                var teacherSemesterAmount = cbs.cbs_teacher_semester_amount.FirstOrDefault(t => t.TeacherId == studentPayment.TeacherId && t.SemesterId == studentPayment.PaymentSemester);
                if (teacherSemesterAmount == null)
                {
                    teacherSemesterAmount = new cbs_teacher_semester_amount
                    {
                        TeacherId = studentPayment.TeacherId,
                        SemesterId = studentPayment.PaymentSemester,
                        Receivable = 0,
                        Reeipts = studentPayment.Payment
                    };

                    cbs.cbs_teacher_semester_amount.Add(teacherSemesterAmount);
                    cbs.SaveChanges();
                }
                else
                {
                    teacherSemesterAmount.Reeipts += studentPayment.Payment;
                    cbs.SaveChanges();
                }


                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }

        }

        public static bool DeleteStudentPayment(int paymentId)
        {
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                var payment = cbs.cbs_student_payment.FirstOrDefault(x => x.PaymentId == paymentId);
                if (payment == null) { transaction.Rollback(); return false; }

                var paymentSemester = cbs.cbs_student_semester_amount.FirstOrDefault(x => x.StudentId == payment.StudentId && x.SemesterId == payment.PaymentSemester);
                if (paymentSemester != null)
                {

                    paymentSemester.Payment -= payment.Payment;
                }
                cbs.SaveChanges();

                if (payment.PaymentPicId != null)
                {
                    var img = cbs.cbs_image.FirstOrDefault(t => t.ImageId == payment.PaymentPicId);
                    if (img != null)
                    {
                        cbs.cbs_image.Remove(img);
                    }
                }

                cbs.cbs_student_payment.Remove(payment);
                cbs.SaveChanges();

                var stuAmount = cbs.cbs_student_amount.FirstOrDefault(x => x.StudentId == payment.StudentId);
                if (stuAmount == null) { transaction.Rollback(); return false; }

                stuAmount.Balance -= payment.Payment;
                cbs.SaveChanges();

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
        }


        public static List<StudentPaymentViewModel> GetList(int semesterId, string keyword, Pagination page)
        {
            string sql = @"select a.*,b.SemesterId,b.SemesterName,c.StudentName,d.TeacherName
from cbs_student_payment a
	left join cbs_semester b on a.PaymentSemester = b.SemesterId
	left join cbs_student c on a.StudentId = c.StudentId
	left join cbs_teacher d on a.TeacherId = d.TeacherId
where (@semesterId = 0 or a.PaymentSemester = @semesterId)
and (@keyword = '' or c.StudentName like @keyword)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<StudentPaymentViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static List<StudentSemesterAmountViewModel> GetStudentAmountSemesterList(int semesterId, string keyword, Pagination page)
        {
            string sql = @"select a.*,b.StudentName,c.SemesterName
from cbs_student_semester_amount a
	left join cbs_student b on a.StudentId = b.StudentId
	left join cbs_semester c on a.SemesterId = c.SemesterId
where (@semesterId = 0 or a.SemesterId = @semesterId)
and (@keyword = '' or b.StudentName like @keyword)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<StudentSemesterAmountViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static StudentPaymentViewModel GetStudentPaymentInfo(int paymentId)
        {
            using CBSContext cbs = new();
            var tmp = cbs.cbs_student_payment.FirstOrDefault(t => t.PaymentId == paymentId);
            var data = new StudentPaymentViewModel()
            {
                PaymentId = tmp.PaymentId,
                BeforeAmount = tmp.BeforeAmount,
                LogTime = tmp.LogTime,
                Payment = tmp.Payment,
                PaymentCode = tmp.PaymentCode,
                PaymentPic = cbs.cbs_image.FirstOrDefault(t => t.ImageId == tmp.PaymentPicId)?.ImageData,
                PaymentSemester = tmp.PaymentSemester,
                PaymentTime = tmp.PaymentTime,
                PaymentType = tmp.PaymentType,
                StudentId = tmp.StudentId,
                TeacherId = tmp.TeacherId,
                SemesterName = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == tmp.PaymentSemester)?.SemesterName,
                StudentName = cbs.cbs_student.FirstOrDefault(t => t.StudentId == tmp.StudentId)?.StudentName,
                TeacherName = cbs.cbs_teacher.FirstOrDefault(t => t.TeacherId == tmp.TeacherId)?.TeacherName,
            };

            return data;
        }
    }
}
